package com.yanyeori.framework.core.util.excel;

import com.monitorjbl.xlsx.impl.StreamingCell;
import com.yanyeori.framework.core.util.StringUtil;
import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.LocaleUtil;
import org.springframework.util.CollectionUtils;

import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.function.Supplier;
import java.util.stream.Collectors;

/**
 * Excel工具类
 *
 * @author chenkuan
 * @since 2022/10/18
 */
public class ExcelUtil {
    /**
     * java date格式的CellType
     */
    private static final List<CellType> DATE_CELL_TYPE = Arrays.asList(CellType.NUMERIC, CellType.FORMULA, CellType.BLANK);

    /**
     * 创建Excel读builder
     */
    public static ExcelReader.ExcelConfig createReader() {
        return ExcelReader.builder();
    }

    /**
     * 创建Excel流式读builder
     */
    public static ExcelStreamReader.ExcelConfig createStreamReader() {
        return ExcelStreamReader.builder();
    }

    /**
     * 创建Excel写builder
     */
    public static ExcelWriter.ExcelConfig createWriter() {
        return ExcelWriter.builder();
    }

    public static <T> RowData<T> readRow(Row row, Supplier<T> supplier, Map<Integer, String> indexFieldMap) throws ReflectiveOperationException {
        T bean = supplier.get();
        //最后一列的列号
        int lastCellNum = row.getLastCellNum();
        //记录列是否不为空值
        boolean allColumnEmpty = true;
        for (Map.Entry<Integer, String> entry : indexFieldMap.entrySet()) {
            //获取对应单元格
            int index = entry.getKey();
            if (index > lastCellNum) {
                continue;
            }
            Cell cell = row.getCell(index);
            String cellStr = toString(cell);
            if (StringUtil.isEmpty(cellStr)) {
                continue;
            }
            //非所有列为空
            allColumnEmpty = false;
            //反射获取bean字段，设置value
            Field field = bean.getClass().getDeclaredField(entry.getValue());
            if (!field.isAccessible()) {
                field.setAccessible(true);
            }
            field.set(bean, transferFromCell(cell, cellStr, field.getType()));
        }
        return new RowData<>(allColumnEmpty, bean);
    }

    public static <T> T transferFromCell(Cell cell, String cellStr, Class<T> fieldType) {
        if (cellStr == null) {
            cellStr = toString(cell);
        }
        if (StringUtil.isEmpty(cellStr)) {
            return null;
        }
        Object result;
        CellType cellType = cell.getCellType();
        //处理科学计数法
        if (cellStr.contains("E")) {
            cellStr = new DataFormatter().formatCellValue(cell);
        }
        //处理整数带.0
        if (cellType == CellType.NUMERIC && cellStr.endsWith(".0")) {
            cellStr = cellStr.substring(0, cellStr.length() - 2);
        }
        cellStr = cellStr.trim();
        if (fieldType == String.class) {
            result = cellStr;
        } else if (fieldType == BigDecimal.class) {
            result = new BigDecimal(cellStr);
        } else if (fieldType == BigInteger.class) {
            result = new BigInteger(cellStr);
        } else if (fieldType == Integer.class || fieldType == int.class) {
            result = Integer.valueOf(cellStr);
        } else if (fieldType == Long.class || fieldType == long.class) {
            result = Long.valueOf(cellStr);
        } else if (fieldType == Date.class) {
            if (DATE_CELL_TYPE.contains(cellType) && DateUtil.isCellDateFormatted(cell)) {
                result = DateUtil.getJavaDate(cell.getNumericCellValue());
            } else {
                result = com.yanyeori.framework.core.util.DateUtil.format(cellStr);
            }
        } else if (fieldType == Double.class || fieldType == double.class) {
            result = Double.valueOf(cellStr);
        } else if (fieldType == Float.class || fieldType == float.class) {
            result = Float.valueOf(cellStr);
        } else if (fieldType == Boolean.class || fieldType == boolean.class) {
            result = Boolean.valueOf(cellStr);
        } else if (fieldType == Short.class || fieldType == short.class) {
            result = Short.valueOf(cellStr);
        } else if (fieldType == Byte.class || fieldType == byte.class) {
            result = Byte.valueOf(cellStr);
        } else if (fieldType == Character.class || fieldType == char.class) {
            result = cellStr.charAt(0);
        } else {
            result = cellStr;
        }
        return (T) result;
    }

    public static void indexFieldMap(Map<Integer, String> indexFieldMap, Class<?> clazz) {
        if (indexFieldMap.isEmpty()) {
            Field[] fields = clazz.getDeclaredFields();
            //Excel注解的字段顺序
            if (indexFieldMap.isEmpty()) {
                for (Field field : fields) {
                    field.setAccessible(true);
                    Excel excel = field.getAnnotation(Excel.class);
                    if (excel != null && excel.index() > 0) {
                        indexFieldMap.put(excel.index() - 1, field.getName());
                    }
                }
            }
            //bean默认字段顺序
            if (indexFieldMap.isEmpty()) {
                for (int i = 0; i < fields.length; i++) {
                    indexFieldMap.put(i, fields[i].getName());
                }
            }
        }
    }

    public static String formatStringText(Object value, String dateFormatter) {
        if (value == null) {
            return null;
        }
        String textValue;
        if (value instanceof Date) {
            textValue = com.yanyeori.framework.core.util.DateUtil.format((Date) value, dateFormatter);
        } else {
            textValue = String.valueOf(value);
        }
        return textValue;
    }

    public static CellStyle getStyleBorder(CellStyle style, boolean lastRow, boolean lastCell) {
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        if (lastCell) {
            style.setBorderRight(BorderStyle.THIN);
        } else {
            style.setBorderRight(BorderStyle.NONE);
        }
        if (lastRow) {
            style.setBorderBottom(BorderStyle.THIN);
        } else {
            style.setBorderBottom(BorderStyle.NONE);
        }
        return style;
    }

    public static String toString(Cell cell) {
        if (cell == null) {
            return null;
        }
        if (cell instanceof StreamingCell) {
            switch (cell.getCellType()) {
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        DateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy", LocaleUtil.getUserLocale());
                        sdf.setTimeZone(LocaleUtil.getUserTimeZone());
                        return sdf.format(cell.getDateCellValue());
                    }
                    return cell.getNumericCellValue() + "";
                case STRING:
                    return cell.getRichStringCellValue().toString();
                case BOOLEAN:
                    return cell.getBooleanCellValue() ? "TRUE" : "FALSE";
                case ERROR:
                    return ErrorEval.getText(cell.getErrorCellValue());
                case _NONE:
                default:
                    return "Unknown Cell Type: " + cell.getCellType();
                case FORMULA:
                    return cell.getCellFormula();
                case BLANK:
                    return "";
            }
        } else {
            return cell.toString();
        }
    }

    public static class RowData<T> {
        private boolean allColumnEmpty;
        private T data;

        public RowData(boolean allColumnEmpty, T data) {
            this.allColumnEmpty = allColumnEmpty;
            this.data = data;
        }

        public boolean isAllColumnEmpty() {
            return allColumnEmpty;
        }

        public RowData<T> setAllColumnEmpty(boolean allColumnEmpty) {
            this.allColumnEmpty = allColumnEmpty;
            return this;
        }

        public T getData() {
            return data;
        }

        public RowData<T> setData(T data) {
            this.data = data;
            return this;
        }
    }

    /**
     * 复制指定区域（含值和样式）
     */
    public static void copyRegion(Sheet sourceSheet, Sheet targetSheet,
                                  int srcStartRow, int srcEndRow, int srcStartCol, int srcEndCol,
                                  int destStartRow, int destStartCol, boolean copyHeightAndWidth) {
        for (int i = srcStartRow; i <= srcEndRow; i++) {
            Row sourceRow = sourceSheet.getRow(i);
            if (sourceRow == null) continue;

            // 创建目标行（相对位置）
            int destRowNum = destStartRow + (i - srcStartRow);
            Row targetRow = targetSheet.getRow(destRowNum);
            if (targetRow == null) {
                targetRow = targetSheet.createRow(destRowNum);
            }
            // 复制行高
            if (copyHeightAndWidth) {
                targetRow.setHeightInPoints(sourceRow.getHeightInPoints());
            }

            for (int j = srcStartCol; j <= srcEndCol; j++) {
                Cell sourceCell = sourceRow.getCell(j);
                if (sourceCell == null) continue;

                // 创建目标单元格（相对位置）
                int destColNum = destStartCol + (j - srcStartCol);
                Cell targetCell = targetRow.createCell(destColNum);

                // 复制单元格内容
                copyCellValue(sourceCell, targetCell);
                // 复制单元格样式（深拷贝）
                CellStyle newStyle = targetSheet.getWorkbook().createCellStyle();
                newStyle.cloneStyleFrom(sourceCell.getCellStyle());
                targetCell.setCellStyle(newStyle);
                // 复制列宽
                if (copyHeightAndWidth) {
                    targetSheet.setColumnWidth(destColNum, sourceSheet.getColumnWidth(j));
                }
            }
        }

        // 复制合并区域（可选）
        copyMergedRegions(sourceSheet, targetSheet,
                srcStartRow, srcEndRow, srcStartCol, srcEndCol,
                destStartRow, destStartCol);
    }

    /**
     * 复制单元格值（支持公式、字符串、数字等）
     */
    public static void copyCellValue(Cell sourceCell, Cell targetCell) {
        switch (sourceCell.getCellType()) {
            case STRING:
                targetCell.setCellValue(sourceCell.getStringCellValue());
                break;
            case NUMERIC:
                targetCell.setCellValue(sourceCell.getNumericCellValue());
                break;
            case BOOLEAN:
                targetCell.setCellValue(sourceCell.getBooleanCellValue());
                break;
            case FORMULA:
                targetCell.setCellFormula(sourceCell.getCellFormula());
                break;
            case BLANK:
                targetCell.setBlank();
                break;
            default:
                targetCell.setCellValue("");
        }
    }

    /**
     * 复制合并区域（调整偏移量）
     */
    private static void copyMergedRegions(Sheet sourceSheet, Sheet targetSheet,
                                          int srcStartRow, int srcEndRow, int srcStartCol, int srcEndCol,
                                          int destStartRow, int destStartCol) {
        int rowOffset = destStartRow - srcStartRow;
        int colOffset = destStartCol - srcStartCol;

        for (CellRangeAddress mergedRegion : sourceSheet.getMergedRegions()) {
            if (mergedRegion.getFirstRow() >= srcStartRow &&
                    mergedRegion.getLastRow() <= srcEndRow &&
                    mergedRegion.getFirstColumn() >= srcStartCol &&
                    mergedRegion.getLastColumn() <= srcEndCol) {

                // 调整合并区域的坐标
                CellRangeAddress newMergedRegion = new CellRangeAddress(
                        mergedRegion.getFirstRow() + rowOffset,
                        mergedRegion.getLastRow() + rowOffset,
                        mergedRegion.getFirstColumn() + colOffset,
                        mergedRegion.getLastColumn() + colOffset
                );
                targetSheet.addMergedRegion(newMergedRegion);
            }
        }
    }

    public static void deleteColumns(Sheet sheet, Collection<Integer> colsToDelete) {
        if (CollectionUtils.isEmpty(colsToDelete)) return;

        // 将列索引从大到小排序（避免删除时索引错位）
        List<Integer> colsToDeleteSorted = colsToDelete.stream().distinct().sorted(Collections.reverseOrder()).collect(Collectors.toList());

        // 获取最大列号
        int originLastColIndex = getLastColIndex(sheet);

        // 记录原始列宽
        Map<Integer, Integer> originalWidths = recordColumnWidths(sheet, originLastColIndex);

        // 收集受影响的合并区域并取消合并
        List<CellRangeAddress> affectedRegions = collectAffectedRegions(sheet, colsToDeleteSorted);

        // 删除指定列
        for (int col : colsToDeleteSorted) {
            // 左移后续列，覆盖当前删除的列
            if (col < originLastColIndex) {
                sheet.shiftColumns(col + 1, originLastColIndex, -1);
            }
        }

        // 调整并恢复合并区域
        restoreMergedRegions(sheet, affectedRegions, colsToDeleteSorted);

        // 调整列宽
        adjustColumnWidths(sheet, colsToDeleteSorted, originalWidths, originLastColIndex);

        // 清除残留列数据、样式
        cleanRemnantColumns(sheet, originLastColIndex, colsToDeleteSorted);
    }

    public static int getLastColIndex(Sheet sheet) {
        int lastColumn = -1;
        for (Row row : sheet) {
            if (row != null) {
                int currentLast = row.getLastCellNum() - 1;
                if (currentLast > lastColumn) {
                    lastColumn = currentLast;
                }
            }
        }
        return lastColumn;
    }

    private static Map<Integer, Integer> recordColumnWidths(Sheet sheet, int originLastColIndex) {
        Map<Integer, Integer> widths = new HashMap<>();
        for (int i = 0; i <= originLastColIndex; i++) {
            widths.put(i, sheet.getColumnWidth(i));
        }
        return widths;
    }

    private static void adjustColumnWidths(Sheet sheet, List<Integer> deletedCols, Map<Integer, Integer> originalWidths, int originLastColIndex) {
        List<Integer> sortedDeleted = deletedCols.stream().sorted().collect(Collectors.toList());

        for (int newCol = 0; newCol <= originLastColIndex; newCol++) {
            int originalCol = calculateOriginalColumn(newCol, sortedDeleted);
            int width = originalWidths.getOrDefault(originalCol, sheet.getDefaultColumnWidth());
            sheet.setColumnWidth(newCol, width);
        }
    }

    private static int calculateOriginalColumn(int newCol, List<Integer> sortedDeleted) {
        int original = newCol;
        for (Integer delCol : sortedDeleted) {
            if (original >= delCol) {
                original++;
            } else {
                break;
            }
        }
        return original;
    }

    private static List<CellRangeAddress> collectAffectedRegions(Sheet sheet, List<Integer> colsToDelete) {
        List<CellRangeAddress> affectedRegions = new java.util.ArrayList<>();

        // 倒序遍历以避免移除元素时的索引问题
        for (int i = sheet.getNumMergedRegions() - 1; i >= 0; i--) {
            CellRangeAddress region = sheet.getMergedRegion(i);
            if (isRegionAffected(region, colsToDelete)) {
                affectedRegions.add(region);
                sheet.removeMergedRegion(i);
            }
        }
        return affectedRegions;
    }

    private static boolean isRegionAffected(CellRangeAddress region, List<Integer> colsToDelete) {
        int startCol = region.getFirstColumn();
        int endCol = region.getLastColumn();
        return colsToDelete.stream().anyMatch(col -> col >= startCol && col <= endCol);
    }

    private static void restoreMergedRegions(Sheet sheet, List<CellRangeAddress> affectedRegions, List<Integer> deletedCols) {
        for (CellRangeAddress region : affectedRegions) {
            CellRangeAddress adjustedRegion = adjustRegion(region, deletedCols);
            if (isValidRegion(adjustedRegion)) {
                sheet.addMergedRegion(adjustedRegion);
            }
        }
    }

    private static CellRangeAddress adjustRegion(CellRangeAddress region, List<Integer> deletedCols) {
        int originalStartCol = region.getFirstColumn();
        int originalEndCol = region.getLastColumn();

        // 计算列偏移量
        long startOffset = deletedCols.stream().filter(c -> c < originalStartCol).count();
        long endOffset = deletedCols.stream().filter(c -> c <= originalEndCol).count();

        int newStartCol = (int) (originalStartCol - startOffset);
        int newEndCol = (int) (originalEndCol - endOffset);

        return new CellRangeAddress(
                region.getFirstRow(),
                region.getLastRow(),
                newStartCol,
                newEndCol
        );
    }

    private static boolean isValidRegion(CellRangeAddress region) {
        return region.getFirstColumn() <= region.getLastColumn()
                && region.getFirstRow() <= region.getLastRow();
    }

    private static void cleanRemnantColumns(Sheet sheet, int originLastColIndex, List<Integer> colsToDelete) {
        int newMaxCol = originLastColIndex - colsToDelete.size();

        // 清理单元格数据（从新最大列+1到原始最大列）
        for (int col = newMaxCol + 1; col <= originLastColIndex; col++) {
            // 清除列数据
            clearColumnData(sheet, col);
            // 重置列宽
            sheet.setColumnWidth(col, sheet.getDefaultColumnWidth());
        }
    }

    private static void clearColumnData(Sheet sheet, int columnIndex) {
        for (Row row : sheet) {
            if (row != null) {
                Cell cell = row.getCell(columnIndex);
                if (cell != null) {
                    row.removeCell(cell);
                }
            }
        }
    }
}
